Check for a missing index

If you think Oracle should be using an index to resolve your query and it is not doing so, then make sure the index exists. Here are two ways to check:

• Using Oracle Enterprise Manager (OEM), open up your database in the Navigator window; open the Schema folder; open the schema that owns your query table; open Tables; find your table name and open it; open Indexes. Click on each index listed, and the indexed column(s) will show in the right hand pane.

• For those who don't have OEM (and those that find it too slow), download show.sql and run it from SQL*Plus as follows:

  @show my_table_name

  where my_table_name is the table you expect to be indexed. show.sql will list the columns in the table, followed by a list of the indexes including their uniqueness, followed by the columns of each index.

If you can't find the table, perhaps it is a VIEW, or a SYNONYM to a table in another schema.

Do any indexes exist? If so, is there one where the leading columns of the index match the columns of your WHERE clause?

If the answer is NO to either of these questions, then the problem is that there is no index that covers your SQL. Perhaps the index has been accidently dropped, or it never existed at all. You have four choices:

1. If the index has been dropped accidently, get the DBA to re-create it.
2. Restructure your query. Perhaps the structure of your database permits you to retrieve the information in another way that does contain an index.
3. How bad is the performance really? Is it unacceptably slow? If not, then doing nothing is a valid course of action. But consider that performance will deteriorate if the table is growing.
4. Discuss with the DBA the prospect of adding a new index. Providing the index is efficient, and you have genuinely considered and rejected the options above, the DBA sould have no objections. However if the index is inefficient, then it will not help your query. You could discuss with the DBA the merits of partitioning, or a hash cluster.